package com.huangdada.hfweather.bean;

import com.huangdada.hfweather.utils.WDWUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.BreakClear;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.FileNameMap;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.SynchronousQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

/**
 * @Description: 读取解析Excel的类
 * @PROJECT_NAME: hf-weather
 * @CLASS_NAME:
 * @Author: huanglibin
 * @Date: 2020-03-31 20:44
 * @Version: 1.0
 */
@Slf4j
public class ReadExcel {

    /**
     * 总行数
     */
    private int totalRows = 0;

    /**
     * 总条数
     */
    private int totalCells = 0;

    /**
     * 错误信息接收器
     */
    private String errorMsg;

    /**
     * 构造方法
     */
    public ReadExcel() {
    }

    /**
     * 得到总行数
     *
     * @return
     */
    public int getTotalRows() {
        return totalRows;
    }

    /**
     * 得到总列数
     *
     * @return
     */
    public int getTotalCells() {
        return totalCells;
    }

    public String getErrorInfo() {
        return errorMsg;
    }

    /**
     * 验证Excel文件
     *
     * @param fileName
     * @return
     */
    public boolean validateExcel(String fileName) {
        if (fileName == null || !(WDWUtil.isExcel2003(fileName) || WDWUtil.isExcel2007(fileName))) {
            log.info("文件名不是Excel格式....");
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    /**
     * 读Excel文件
     *
     * @param fileName
     * @param Mfile
     * @return
     */
    public List<City> getExcelInfo(String fileName, MultipartFile Mfile) {
        File dfile = null;
        try {
            dfile = File.createTempFile("perfix", "_" + fileName);
            Mfile.transferTo(dfile);
        } catch (IOException e) {
            e.printStackTrace();
        }

        File file = dfile;

        List<City> cityList = new ArrayList<>();
        InputStream is = null;
        try {
            //验证文件是否合格
            if (!validateExcel(fileName)) {
                return null;
            }
            //判断是2003版还是2007版
            boolean isExcel2003 = true;
            if (WDWUtil.isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            is = new FileInputStream(file);
            cityList = getExcelInfo(is, isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
            log.info("读取Excel文件错误.....,error：" + e);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return cityList;
    }

    /**
     * InputStream是字节流。isExcel2003是excel是2003还是2007版本
     *
     * @param is
     * @param isExcel2003
     * @return
     */
    public List<City> getExcelInfo(InputStream is, boolean isExcel2003) {
        List<City> cityList = null;
        try {
            /*根据版本创建workbook方式*/
            Workbook wb = null;
            //当Excel是2003时
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            cityList = readExcelValue(wb);
        } catch (IOException e) {
            e.printStackTrace();
            log.info("读取Excel信息失败！");
        }
        return cityList;
    }

    /**
     * 读取Excel里面的信息
     *
     * @param wb
     * @return
     */
    public List<City> readExcelValue(Workbook wb) {
        //得到第一个shell
        Sheet sheet = wb.getSheetAt(0);

        //得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();

        //得到Excel的列数（前提是有行数）
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(1).getPhysicalNumberOfCells();
        }

        List<City> cityList = new ArrayList<>();
        City city;

        //循环Excel行数，从第二行开始，标题不入库
        for (int i = 2; i < this.totalRows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            city = new City();
            //循环Excel的列
            for (int j = 0; j < this.totalCells; j++) {
                Cell cell = row.getCell(j);
                if (null != cell) {
                    switch (j) {
                        case 0:
                            city.setCityId(cell.getStringCellValue());
                            break;
                        case 1:
                            city.setCityEn(cell.getStringCellValue());
                            break;
                        case 2:
                            city.setCityCn(cell.getStringCellValue());
                            break;
                        case 3:
                            city.setCountryCode(cell.getStringCellValue());
                            break;
                        case 4:
                            city.setCountryEn(cell.getStringCellValue());
                            break;
                        case 5:
                            city.setCountryCn(cell.getStringCellValue());
                            break;
                        case 6:
                            city.setProvinceEn(cell.getStringCellValue());
                            break;
                        case 7:
                            city.setProvinceCn(cell.getStringCellValue());
                            break;
                        case 8:
                            city.setAdminDistrictEn(cell.getStringCellValue());
                            break;
                        case 9:
                            city.setAdminDistrictCn(cell.getStringCellValue());
                            break;
                        case 10:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            city.setLatitude(cell.getStringCellValue());
                            break;
                        case 11:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            city.setLongitude(cell.getStringCellValue());
                            break;
                        case 12:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            city.setAdCode(cell.getStringCellValue());
                            break;
                        default:
                            break;
                    }
                }
            }
            cityList.add(city);
        }
        return cityList;
    }
}
